//This program handles logging in onto Google Services
package com.gmft.tablemanager;

/**
 *
 * @author Jonnie T
 */


//Required Google Packages (GDATA and GUAVA)
import com.google.gdata.client.ClientLoginAccountType;
import com.google.gdata.client.GoogleService;
import com.google.gdata.client.Service.GDataRequest;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ContentType;
import com.google.gdata.util.ServiceException;

//Java Required Packages
import java.io.*;
import java.net.URL;
import java.net.URLEncoder;

public class GoogleFusionTableDB 
{
    //Constant - ACCESS To Fusion Table API
static final String URL_FUSIONTABLEAPI 
        = "https://www.google.com/fusiontables/api/query";    

GoogleService service;

    public GoogleFusionTableDB(String loginfile)
    {
        try
        {
        FileReader credFile = new FileReader(loginfile);
        BufferedReader credBuff=new BufferedReader (credFile);

        String gmailAC=credBuff.readLine();
        String gmailPswd=credBuff.readLine();
        
        credFile.close();
        
        System.out.println("Gmail Account: " + gmailAC);
        System.out.println("Password entered");
        
        System.out.println("Logging in to Google Service");
        service = new GoogleService("fusiontables","fusiontables.BuildAndControl");
        //Will throw AuthenticationException if login fails
        service.setUserCredentials(gmailAC, gmailPswd, ClientLoginAccountType.GOOGLE);
        }
        catch (AuthenticationException a)
        {
            System.out.println("Could not Authenticate with Google: " +a);
        }
        catch (FileNotFoundException f)
        {
            System.out.println("No File "+loginfile+" exists: " +f);
        }
        catch (IOException i)
        {
            System.out.println("IO Error: " +i);
        }
        
        System.out.println("Logged in to Google Service");
    }
    
    // This method executes the GET Query to Fusion Maps Table   
    public String getQuery(String sqlQuery, boolean encryptedID) 
    {
        String finalLine = "NULL_ERROR";
        try
        {
        String UTF8EncodedURL=URLEncoder.encode(sqlQuery, "UTF-8");
        
        GDataRequest gdatareq;
        URL submitURL;
        
        //PERFORM GET QUERY
        submitURL=new URL(URL_FUSIONTABLEAPI + "?sql=" + UTF8EncodedURL
                 + "&encid=" + encryptedID);
            //Query sent to GDATA
        gdatareq=service.getRequestFactory().getRequest
          (GDataRequest.RequestType.QUERY, submitURL,
                ContentType.TEXT_PLAIN);
        
        gdatareq.execute();
        
        InputStreamReader inStream = new InputStreamReader(gdatareq.getResponseStream());
        BufferedReader results = new BufferedReader(inStream);
        
        //Gives just column name
        String line= results.readLine();
        finalLine = line;
        while (line!=null)
        {
        line=results.readLine();
        finalLine=finalLine+line;
        }
        }
        catch(ServiceException e)
        {
            System.out.println ("Error in SQL Command: " + e);
        }
        catch(IOException i)
        {
            System.out.println ("Error in Creating Stream: " + i);
        }
        return finalLine;
        
    }
    
    // This method executes the POST Query to Fusion Maps Table     
    public boolean setQuery(String sqlQuery, boolean encryptedID) 
    {
        //boolean success= true;
        
        try
        {
        String UTF8EncodedURL=URLEncoder.encode(sqlQuery, "UTF-8");
        
        GDataRequest gdatareq;
        URL submitURL;
        
        //PERFORM SET QUERY
        submitURL = new URL(URL_FUSIONTABLEAPI + "?encid=" + encryptedID);
            gdatareq = service.getRequestFactory().getRequest
            (GDataRequest.RequestType.INSERT, submitURL,
            new ContentType("application/x-www-form-urlencoded"));
            
            //
        OutputStreamWriter writer = new OutputStreamWriter(gdatareq.getRequestStream());
        writer.append("sql=" + UTF8EncodedURL);
        writer.flush(); 
        
        gdatareq.execute();
        }
        catch(ServiceException e)
        {
            //success=false;
            System.err.println("Error in Get SQL Query: " +e);            
            return false;
        }
        catch(IOException f)
        {
            //success=false;
            System.err.println("Error in creating Stream: " +f);
            return false;
        }        
        //return success;
        return true;
    }
    
    public String extractRowID(String tableEnc,String field,String fval) 
    {
        boolean encryptedID= true;
        String row_id ="ERROR_IN_EXTRACT";
        String sqlQuery = "SELECT ROWID FROM " + tableEnc + " WHERE " +field+ " = '"+fval+"'";
               
        try
        {
        
        String UTF8EncodedURL=URLEncoder.encode(sqlQuery, "UTF-8");
        
        GDataRequest gdatareq;
        URL submitURL;
        
        //PERFORM GET QUERY
        submitURL=new URL(URL_FUSIONTABLEAPI + "?sql=" + UTF8EncodedURL
                 + "&encid=" + encryptedID);
            //Query sent to GDATA
            
        gdatareq=service.getRequestFactory().getRequest
          (GDataRequest.RequestType.QUERY, submitURL,
                ContentType.TEXT_PLAIN);        
        gdatareq.execute(); 
        
        
        InputStreamReader inStream = new InputStreamReader(gdatareq.getResponseStream());
        BufferedReader results = new BufferedReader(inStream);
        
        //Gives just column name
        results.readLine();
        //Actual Row ID
        row_id=results.readLine();
        
        
        }
        catch (ServiceException e)
        {
        //
            System.err.println("Error in Get SQL Query: " +e);
        }
        catch (IOException f)
        {
            System.err.println("Error in SQL Result Stream: " +f);
        }      
        
        return row_id;
    }
    
    
}
